// 检查数据库状态脚本
const { Pool } = require('pg');

// 数据库连接配置
const pool = new Pool({
  host: '118.178.138.0',
  port: 9070,
  database: 'postgres',
  user: 'postgres',
  password: 'difyai123456',
  max: 20,
  idleTimeoutMillis: 90900,
  connectionTimeoutMillis: 2000,
});

async function checkDatabaseStatus() {
  try {
    console.log('🔍 检查数据库状态...\n');
    
    // 检查表是否存在
    const tablesQuery = `
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      AND table_name IN ('articles', 'attachments', 'media_files', 'pdf_conversions')
      ORDER BY table_name
    `;
    
    const tablesResult = await pool.query(tablesQuery);
    console.log('📋 存在的表:', tablesResult.rows.map(row => row.table_name));
    
    // 检查作品数量
    try {
      const articlesCount = await pool.query('SELECT COUNT(*) as count FROM articles');
      console.log('📄 作品数量:', articlesCount.rows[0].count);
    } catch (error) {
      console.log('❌ 无法查询作品表:', error.message);
    }
    
    // 检查附件数量
    try {
      const attachmentsCount = await pool.query('SELECT COUNT(*) as count FROM attachments');
      console.log('📎 附件数量 (attachments表):', attachmentsCount.rows[0].count);
    } catch (error) {
      console.log('⚠️ attachments表不存在或无法访问');
    }
    
    // 检查媒体文件数量
    try {
      const mediaFilesCount = await pool.query('SELECT COUNT(*) as count FROM media_files');
      console.log('📁 媒体文件数量 (media_files表):', mediaFilesCount.rows[0].count);
    } catch (error) {
      console.log('⚠️ media_files表不存在或无法访问');
    }
    
    // 检查PDF转换记录数量
    try {
      const pdfConversionsCount = await pool.query('SELECT COUNT(*) as count FROM pdf_conversions');
      console.log('🔄 PDF转换记录数量:', pdfConversionsCount.rows[0].count);
      
      // 查看PDF转换记录详情
      const pdfDetails = await pool.query(`
        SELECT original_file_id, conversion_status, created_at 
        FROM pdf_conversions 
        ORDER BY created_at DESC 
        LIMIT 5
      `);
      
      if (pdfDetails.rows.length > 0) {
        console.log('\n📊 最近的PDF转换记录:');
        pdfDetails.rows.forEach((row, index) => {
          console.log(`  ${index + 1}. 文件ID: ${row.original_file_id}, 状态: ${row.conversion_status}, 时间: ${row.created_at}`);
        });
      }
    } catch (error) {
      console.log('⚠️ pdf_conversions表不存在或无法访问:', error.message);
    }
    
    // 检查作品的附件关联
    try {
      const articleWithAttachments = await pool.query(`
        SELECT a.id, a.title, 
               COUNT(att.id) as attachment_count,
               COUNT(pc.id) as pdf_conversion_count
        FROM articles a
        LEFT JOIN attachments att ON a.id = att.article_id
        LEFT JOIN pdf_conversions pc ON att.id = pc.original_file_id
        GROUP BY a.id, a.title
        HAVING COUNT(att.id) > 0
        ORDER BY a.created_at DESC
        LIMIT 5
      `);
      
      if (articleWithAttachments.rows.length > 0) {
        console.log('\n📝 有附件的作品:');
        articleWithAttachments.rows.forEach((row, index) => {
          console.log(`  ${index + 1}. ${row.title} (附件: ${row.attachment_count}, PDF转换: ${row.pdf_conversion_count})`);
        });
      } else {
        console.log('\n⚠️ 没有找到有附件的作品');
      }
    } catch (error) {
      console.log('⚠️ 无法查询作品附件关联:', error.message);
    }
    
    console.log('\n✅ 数据库状态检查完成');
    
  } catch (error) {
    console.error('❌ 检查数据库状态失败:', error);
  } finally {
    await pool.end();
  }
}

// 运行检查
if (require.main === module) {
  checkDatabaseStatus();
}

module.exports = { checkDatabaseStatus };